package service;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.ArrayList;
import java.util.List;


@WebServlet("/aa")
//11
public class SemesterGender_Servlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        List<SemesterGenderStat> stats = getGenderStatsFromDB();
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(stats);

        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8"); // 确保字符编码一致
        response.getWriter().write(json);
    }

    private List<SemesterGenderStat> getGenderStatsFromDB() {
        List<SemesterGenderStat> stats = new ArrayList<>();
        String jdbcUrl = "jdbc:mysql://localhost:3306/stats?serverTimezone=Asia/Shanghai";
        String dbUser = "root";
        String dbPassword = "123456";


        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection conn = DriverManager.getConnection(jdbcUrl, dbUser, dbPassword);
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT semester, gender, SUM(count) as total FROM gender_stats0 GROUP BY semester, gender")) {

                while (rs.next()) {
                    stats.add(new SemesterGenderStat(rs.getInt("semester"), rs.getString("gender"), rs.getInt("total")));
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
            return stats;

    }
}

class SemesterGenderStat {
    private int semester;
    private String gender;
    private int count;

    public SemesterGenderStat(int semester, String gender, int count) {
        this.semester = semester;
        this.gender = gender;
        this.count = count;
    }

    // Getters and setters...
    public int getSemester() { return semester; }
    public String getGender() { return gender; }
    public int getCount() { return count; }
}